# SQL 数据库操作封装（连接、查询、增删改等）
import logging
import pymysql
from typing import List, Dict, Any, Optional, Union, Tuple
import configparser
import os
#from cryptography.fernet import Fernet  # Added proper import for cryptography


class MySQLClient:
    def __init__(self,
                 host: Optional[str] = None,
                 user: Optional[str] = None,
                 password: Optional[str] = None,
                 database: Optional[str] = None,
                 port: int = 3306,
                 charset: str = 'utf8mb4',
                 config_path: Optional[str] = None):
        """
        MySQL客户端封装

        参数:
            host: 数据库主机
            user: 用户名
            password: 密码
            database: 数据库名
            port: 端口号，默认3306
            charset: 字符集，默认utf8mb4
            config_path: 可选的配置文件路径
        """
        # 初始化日志
        self.logger = logging.getLogger('MySQLClient')
        self.logger.setLevel(logging.INFO)
        handler = logging.StreamHandler()
        formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
        handler.setFormatter(formatter)
        self.logger.addHandler(handler)

        # 获取配置
        self.host, self.user, self.password, self.database = self._get_db_config(
            host, user, password, database, config_path
        )
        self.port = port
        self.charset = charset

        self.connection = None
        self.cursor = None
        self._connect()

    def _connect(self):
        """建立数据库连接"""
        try:
            self.connection = pymysql.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                port=self.port,
                charset=self.charset,
                cursorclass=pymysql.cursors.DictCursor,
                autocommit=False  # 使用手动事务
            )
            self.cursor = self.connection.cursor()
            self.logger.info(f"成功连接到MySQL数据库: {self.user}@{self.host}:{self.port}/{self.database}")
        except pymysql.Error as e:
            self.logger.error(f"数据库连接失败: {e}")
            raise ConnectionError(f"无法连接到数据库: {e}")

    def _get_db_config(self,
                       host: Optional[str],
                       user: Optional[str],
                       password: Optional[str],
                       database: Optional[str],
                       config_path: Optional[str] = None) -> Tuple[str, str, str, str]:
        """获取数据库配置"""
        # 如果所有参数都已提供，直接返回
        if all([host, user, password, database]):
            return host, user, password, database

        # 从配置文件读取
        config_file = config_path if config_path else self._find_config_file()
        if not config_file:
            raise FileNotFoundError("未找到配置文件config.ini")

        config = configparser.ConfigParser()
        config.read(config_file, encoding='utf-8')

        if 'database' not in config:
            raise ValueError("配置文件中缺少[database]节")

        db_config = config['database']
        return (
            host or db_config.get('host', 'localhost'),
            user or db_config.get('user', 'root'),
            password or db_config.get('password', ''),
            database or db_config.get('database', '') or db_config.get('dbname', '')
        )

    def _find_config_file(self) -> Optional[str]:
        """查找配置文件"""
        # 在常见位置查找配置文件
        search_paths = [
            'config.ini',
            'config/config.ini',
            '../config.ini',
            '../config/config.ini',
            os.path.expanduser('~/.config/mysql_client/config.ini'),
            r'C:\PythonProject\pytest_sql_api_auto\config\config.ini'
        ]

        for path in search_paths:
            if os.path.exists(path):
                return path
        return None

    def execute(self, sql: str, params: Union[Tuple, List, Dict] = (), commit: bool = False):
        """执行SQL语句"""
        try:
            self.cursor.execute(sql, params)
            if commit:
                self.connection.commit()
            return self.cursor
        except pymysql.Error as e:
            self.logger.error(f"执行SQL失败: {e}\nSQL: {sql}\nParams: {params}")
            if self.connection:
                self.connection.rollback()
            raise

    def insert(self, table: str, data: Dict[str, Any]) -> int:
        """插入单条数据"""
        cols = ','.join([f"`{k}`" for k in data.keys()])  # 使用反引号包裹列名
        vals = ','.join(['%s'] * len(data))
        sql = f"INSERT INTO `{table}` ({cols}) VALUES ({vals})"
        self.execute(sql, tuple(data.values()), commit=True)
        return self.cursor.lastrowid

    def batch_insert(self, table: str, data_list: List[Dict[str, Any]]) -> int:
        """批量插入数据"""
        if not data_list:
            return 0

        keys = data_list[0].keys()
        cols = ','.join([f"`{k}`" for k in keys])
        vals = ','.join(['%s'] * len(keys))
        sql = f"INSERT INTO `{table}` ({cols}) VALUES ({vals})"

        params = [tuple(item.values()) for item in data_list]
        self.cursor.executemany(sql, params)
        self.connection.commit()
        return self.cursor.rowcount

    def select(self, table: str, where: str = None, params: Union[Tuple, List, Dict] = (),
               fields: str = "*", limit: Optional[int] = None) -> List[Dict]:
        """查询数据"""
        sql = f"SELECT {fields} FROM `{table}`"
        if where:
            sql += f" WHERE {where}"
        if limit:
            sql += f" LIMIT {limit}"
        self.execute(sql, params)
        return self.cursor.fetchall()

    def select_one(self, table: str, where: str = "", params: Union[Tuple, List, Dict] = (),
                   fields: str = "*") -> Optional[Dict]:
        """查询单条数据"""
        sql = f"SELECT {fields} FROM `{table}`"
        if where:
            sql += f" WHERE {where}"
        sql += " LIMIT 1"
        self.execute(sql, params)
        return self.cursor.fetchone()

    def update(self, table: str, data: Dict[str, Any], where: str, params: Union[Tuple, List, Dict] = ()) -> int:
        """更新数据"""
        sets = ','.join([f"`{k}`=%s" for k in data.keys()])
        sql = f"UPDATE `{table}` SET {sets} WHERE {where}"
        all_params = tuple(data.values()) + (params if isinstance(params, tuple) else (params,))
        self.execute(sql, all_params, commit=True)
        return self.cursor.rowcount

    def delete(self, table: str, where: str, params: Union[Tuple, List, Dict] = ()) -> int:
        """删除数据"""
        sql = f"DELETE FROM `{table}` WHERE {where}"
        self.execute(sql, params, commit=True)
        return self.cursor.rowcount

    def begin_transaction(self):
        """开始事务"""
        self.connection.begin()

    def commit(self):
        """提交事务"""
        self.connection.commit()

    def rollback(self):
        """回滚事务"""
        self.connection.rollback()

    def close(self):
        """关闭连接"""
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
        self.logger.info("数据库连接已关闭")

    def __enter__(self):
        """支持with语句"""
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """支持with语句"""
        self.close()
        if exc_type:  # 如果有异常发生
            self.logger.error(f"发生异常: {exc_val}")

    def read(self, pytest_ini_path, encoding):
        pass


